package io.emqx.cloud;
import java.sql.*;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSON;
import io.emqx.cloud.entity.TempHum2;
import io.emqx.cloud.entity.TopicEntity;
import io.emqx.cloud.util.TimeUtil;

public class SaveMysql {
    private static String driver="com.mysql.jdbc.Driver";
    private static String ip="139.196.8.79";
    // 可能会需要加上useSSL=false
    private static String url="jdbc:mysql://"+ip+":3306/emqx?serverTimezone=UTC&useSSL=false";

    private static String user="root";
    private static String password="xxx";


    Connection conn=null;
    Statement stmt=null;


//    使用之前写入数据库的java代码
    public void savedate(int Qos,String payload) {
        
        java.sql.Timestamp timestamp=new java.sql.Timestamp(System.currentTimeMillis());
        JSONObject msg =JSON.parseObject(payload);

		String sql =
                "insert into temp_hum(temp, hum, qos, up_timestamp)"+
                        "values('"+msg.getInteger("temperature")+"' ,'"
                        +msg.getInteger("humidity")+"','"+Qos+"','"+timestamp+"');";
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(url,user,password);
            stmt=conn.createStatement();
            stmt.executeUpdate(sql);
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                if(stmt!=null) stmt.close();
                if(conn!=null) conn.close();
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
    }




    public void saveTempHum2(TempHum2 tempHum2) throws SQLException {

        System.out.println("保存数据库");
//        java.sql.Timestamp timestamp=new java.sql.Timestamp(System.currentTimeMillis());
//        JSONObject msg =JSON.parseObject(payload);
        String sql = "   INSERT INTO temp_hum2 ( stu_num,name,terminal_name,hum,temp,time,username )\n" +
                "        VALUES\n" +
                "                (\n" +
                "                        ?,?,?,?,?,?,?\n" +
                "                );";

//        String sql =
//                "insert into temp_hum(temp, hum, qos, up_timestamp)"+
//                        "values('"+msg.getInteger("temperature")+"' ,'"+msg.getInteger("humidity")+"','"+Qos+"','"+timestamp+"');";
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(url,user,password);

            PreparedStatement preparedStatement=conn.prepareStatement(sql);
            int i=1;
            preparedStatement.setInt(i++,tempHum2.getStuNum());
            preparedStatement.setString(i++,tempHum2.getName());
            preparedStatement.setString(i++,tempHum2.getTerminalName());
            preparedStatement.setDouble(i++,tempHum2.getHum());
            preparedStatement.setDouble(i++,tempHum2.getTemp());
            preparedStatement.setTimestamp(i++,    TimeUtil.dateToSqlTime(tempHum2.getTime()));
            preparedStatement.setString(i++,tempHum2.getUsername());



//            stmt=conn.createStatement();
//            stmt.executeUpdate(sql);
            preparedStatement.executeUpdate();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                if(stmt!=null) stmt.close();
                if(conn!=null) conn.close();
//                if(conn!=null) conn.close();
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
    }



    public void save(TopicEntity topicEntity) throws SQLException {
//        INSERT INTO temp_hum2 ( stu_num,name,terminal_name,hum,temp,time,username )
//        VALUES
//                (
//                        '','','','','','',''
//                );

        System.out.println("保存数据库");
//        java.sql.Timestamp timestamp=new java.sql.Timestamp(System.currentTimeMillis());
//        JSONObject msg =JSON.parseObject(payload);
        String sql = "insert into topic( topic, qos, payload,time)"+
                "values( ?,?,?,?)";

//        String sql =
//                "insert into temp_hum(temp, hum, qos, up_timestamp)"+
//                        "values('"+msg.getInteger("temperature")+"' ,'"+msg.getInteger("humidity")+"','"+Qos+"','"+timestamp+"');";
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(url,user,password);

            PreparedStatement preparedStatement=conn.prepareStatement(sql);
            int i=1;
            preparedStatement.setString(i++,topicEntity.getTopic());
            preparedStatement.setInt(i++,topicEntity.getQos());
            preparedStatement.setString(i++,topicEntity.getPayload());
            preparedStatement.setTimestamp(i++,    TimeUtil.dateToSqlTime(topicEntity.getTime()));



//            stmt=conn.createStatement();
//            stmt.executeUpdate(sql);
            preparedStatement.executeUpdate();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                if(stmt!=null) stmt.close();
                if(conn!=null) conn.close();
//                if(conn!=null) conn.close();
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
    }
}
